package cn.edu.nwafu.nic.nucleic_acid_test.dao;
import cn.edu.nwafu.nic.nucleic_acid_test.entities.FormData;
import cn.edu.nwafu.nic.nucleic_acid_test.entities.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;
import java.util.HashMap;


/**
 * @author: SungLee
 * @date: 2022-02-21 08:25
 * @description: 表单结果数据mapper接口
 */
@Mapper
public interface FormDataDao extends BaseMapper<HashMap<String,Object>> {

    /**
     * 检测总数
     * */
    @Select("select total_test_num from v_counts")
    Integer testedCount();

    /**
     * 教职工核酸检测统计汇总
     * */
    @Select("select sum(total_num) as total_num,sum(tested_num) as tested_num,sum(no_test_num) as no_test_num\n" +
            "from v_teachers_statistics")
    HashMap<String,Object> jzgCount();

    /**
     * 本科生核酸检测统计汇总
     * */
    @Select("select sum(total_num) as total_num,sum(tested_num) as tested_num,sum(no_test_num) as no_test_num\n" +
            "from v_undergraduate_statistics")
    HashMap<String,Object> bksCount();

    /**
     * 研究生核酸检测统计汇总
     * */
    @Select("select sum(total_num) as total_num,sum(tested_num) as tested_num,sum(no_test_num) as no_test_num\n" +
            "from v_graduate_statistics")
    HashMap<String,Object> yjsCount();

    /**
     * 教职工核酸检测按部门统计
     * */
    @Select("select * from v_teachers_statistics")
    ArrayList<HashMap<String,Object>> jzgTestedStatistics();

    /**
     * 本科生核酸检测按部门统计
     * */
    @Select("select * from v_undergraduate_statistics")
    ArrayList<HashMap<String,Object>> bksTestedStatistics();

    /**
     * 研究生核酸检测按部门统计
     * */
    @Select("select * from v_graduate_statistics")
    ArrayList<HashMap<String,Object>> yjsTestedStatistics();

    /**
     * 教职工未检测人员列表
     * @param dept: null或空串时默认查询所有数据
     * */
    @Select("select * from v_teachers_not_test_list where 1=1 and if(#{dept} is null || #{dept} = '',1=1,dept = #{dept})")
    ArrayList<HashMap<String,Object>> jzgNotTestList(@Param("dept") String dept);

    /**
     * 本科生未检测人员列表
     * @param dept: null或空串时默认查询所有数据
     * */
    @Select("select * from v_undergraduate_not_test_list where 1=1 and if(#{dept} is null || #{dept} = '',1=1,dept = #{dept})")
    ArrayList<HashMap<String,Object>> bksNotTestList(@Param("dept") String dept);

    /**
     * 研究生未检测人员列表
     * @param dept: null或空串时默认查询所有数据
     * */
    @Select("select * from v_graduate_not_test_list where 1=1 and if(#{dept} is null || #{dept} = '',1=1,dept = #{dept})")
    ArrayList<HashMap<String,Object>> yjsNotTestList(@Param("dept") String dept);

    /**
     * 教职工已检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> jzgTestedCountByDept();

    /**
     * 本科生已检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> bksTestedCountByDept();

    /**
     * 研究生已检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> yjsTestedCountByDept();

    /**
     * 教职工未检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> jzgNotTestCountByDept();

    /**
     * 本科生未检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> bksNotTestCountByDept();

    /**
     * 研究生未检测人数（按部门）
     * */
    ArrayList<HashMap<String,Object>> yjsNotTestCountByDept();

    /**
     * 教职工已检测人员列表
     * */
    ArrayList<HashMap<String,Object>> jzgTestedList();

    /**
     * 本科生已检测人员列表
     * */
    ArrayList<HashMap<String,Object>> bksTestedList();

    /**
     * 研究生已检测人员列表
     * */
    ArrayList<HashMap<String,Object>> yjsTestedList();

    @Select("SELECT\n" +
            " b.uid AS '内部编号',\n" +
            " b.user_number AS '学工号',\n" +
            " c.realname AS '姓名',\n" +
            "CASE\n" +
            "  b.role \n" +
            "  WHEN 0 THEN\n" +
            "  '未知' \n" +
            "  WHEN 2 THEN\n" +
            "  '校外人员' \n" +
            "  WHEN 10 THEN\n" +
            "  '教职工' \n" +
            "  WHEN 60 THEN\n" +
            "  '其他人员' \n" +
            "  WHEN 421 THEN\n" +
            "  '普通本科生' \n" +
            "  WHEN 422 THEN\n" +
            "  '成人本科生' \n" +
            "  WHEN 431 THEN\n" +
            "  '硕士研究生' \n" +
            "  WHEN 432 THEN\n" +
            "  '博士研究生' ELSE '其他' \n" +
            " END AS '身份',\n" +
            " c.mobile AS '手机号',\n" +
            " e.NAME AS '所在部门' \n" +
            "FROM\n" +
            " ( SELECT distinct  member_id FROM tag_member WHERE tag_id = 6700 ) a\n" +
            " JOIN user_role b ON a.member_id = b.uid\n" +
            " JOIN user_real c ON b.uid = c.uid\n" +
            " JOIN user_department d ON b.uid = d.uid\n" +
            " JOIN department e ON d.department_id = e.id \n" +
            "WHERE\n" +
            " b.sfzx = 1\n" +
            " AND\n" +
            " (b.uid NOT IN (select uid from form_data_128))\n" +
            "   AND\n" +
            " (b.uid NOT IN ( SELECT member_id FROM tag_member WHERE tag_id = 6437 OR tag_id = 2071 ) )\n" +
            " AND\n" +
            "   (c.mobile NOT IN (select 3nindeshoujihaoma as phone FROM form_data_129))\n" +
            "  AND\n" +
            "  (b.user_number NOT IN (select 6xuegonghao FROM form_data_129 WHERE 6xuegonghao IS NOT NULL AND LENGTH(TRIM(6xuegonghao))>3 ))\n" +
            "ORDER BY e.NAME ;")
    ArrayList<HashMap> listFormAll();
}

